package com.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.model.DocumentInfo;
import com.vo.CollegeCountVo;
import com.vo.DayCollegeCountVo;
import com.vo.DayCountVo;
import com.vo.SourceNumVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface DocumentInfoMapper extends BaseMapper<DocumentInfo> {
    /**
     * 日期分组
     */
    @Select("SELECT DATE_FORMAT(Date(release_time), '%Y-%m-%d') day_time, COUNT(*) num\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "GROUP BY DATE_FORMAT(Date(release_time), '%Y-%m-%d')")
    List<DayCountVo> queryDocumentInfoByDay(@Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 日期学院分组
     */
    @Select("SELECT release_time, DATE_FORMAT(release_time, '%Y-%m-%d') day_time, `source`, COUNT(*) num\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "GROUP BY DATE_FORMAT(release_time, '%Y-%m-%d'), `source`")
    List<DayCollegeCountVo> queryDocumentInfoByDayAndCollege(@Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 数据来源分析
     */
    @Select("SELECT `source`, COUNT(*) num\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "GROUP BY `source`")
    List<CollegeCountVo> queryDocumentInfoByCollege(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT *\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime}) AND DATE(release_time) <= DATE(#{endTime})")
    List<DocumentInfo> query(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT `source`, COUNT(*) num\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "AND search_keyword LIKE CONCAT('%', #{type}, '%')\n" +
            "GROUP BY `source`\n" +
            "ORDER BY num DESC\n" +
            "LIMIT 0,100")
    List<SourceNumVo> querySourceTop(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("type") String type);

    @Select("SELECT search_keyword AS `source`, COUNT(*) num\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "GROUP BY `search_keyword`\n" +
            "ORDER BY num DESC")
    List<SourceNumVo> queryTypeTop(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select("SELECT *\n" +
            "FROM document_summary\n" +
            "WHERE DATE(release_time) >= DATE(#{startTime})\n" +
            "AND DATE(release_time) <= DATE(#{endTime})\n" +
            "AND search_keyword LIKE CONCAT('%', #{type}, '%')\n" +
            "ORDER BY download_count DESC\n" +
            "LIMIT 0,100")
    List<DocumentInfo> queryDownloadTop(@Param("startTime") String startTime, @Param("endTime") String endTime, @Param("type") String type);

}
